package br.com.sisgerenciamento.dao.recebimento;

import java.util.ArrayList;
import java.util.Date;

import org.hibernate.Criteria;
import org.hibernate.Hibernate;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.criterion.Restrictions;
import org.hibernate.transform.AliasToBeanResultTransformer;

import br.com.sisgerenciamento.dto.recebimento.RecebimentoDTO;
import br.com.sisgerenciamento.dto.recebimento.RelatorioRecebimentoDTO;
import br.com.sisgerenciamento.exceptions.AlterarException;
import br.com.sisgerenciamento.exceptions.ApplicationException;
import br.com.sisgerenciamento.exceptions.InserirException;
import br.com.sisgerenciamento.generic.GenericDAO;
import br.com.sisgerenciamento.model.recebimento.RecebimentoVO;
import br.com.sisgerenciamento.utilidades.TreatDate;

public class RecebimentoDAOServiceImpl extends GenericDAO< RecebimentoVO > implements RecebimentoDAOService{

	public RecebimentoVO inserir(RecebimentoVO vo) throws InserirException {
		return (RecebimentoVO) insert(vo);
	}

	public void alterar(RecebimentoVO vo) throws AlterarException {
		update(vo);
	}

	public RecebimentoVO consultarPorCodigo(Long codigo) throws ApplicationException {
		Session session = null;
		try {
			session = getSessionAberta();
			Criteria criteria = session.createCriteria(RecebimentoVO.class);
			criteria.add(Restrictions.eq("codigo", codigo));
			RecebimentoVO vo = (RecebimentoVO) consultar(criteria);
			return vo;
		} catch (Exception e) {
			throw new ApplicationException(e.getMessage());
		} finally {
			if (session != null)
				session.close();
		}
	}
	
	@SuppressWarnings("unchecked")
	public ArrayList<RecebimentoVO> listarPendentes() throws ApplicationException {
		Session session = null;
		try {
			session = getSessionAberta();
			Criteria criteria = session.createCriteria(RecebimentoVO.class);
			criteria.add(Restrictions.eq("pendente", true));
			criteria.createAlias("pedido", "p");
			return (ArrayList<RecebimentoVO>) criteria.list();
		} catch (Exception e) {
			throw new ApplicationException(e.getMessage());
		} finally {
			if (session != null)
				session.close();
		}
	}

	@SuppressWarnings("unchecked")
	public ArrayList<RelatorioRecebimentoDTO> consultarRecebimentosPagos(Date dataInicial, Date dataFinal) throws ApplicationException {

		Session session = null;
		StringBuilder sql = new StringBuilder();

		sql.append("select pedido_id as numeroPedido, p.dataEntrega, c.nome , p.total as totalPedido, sum(valor) as valorPago, (p.total - sum(valor)) as valorPendente from recebimento r ");
		sql.append("JOIN pedido p on p.codigo = r.pedido_id ");
		sql.append("JOIN cliente c on c.codigo = p.cliente_codigo ");
		sql.append(" where r.pendente = false ");
		sql.append("and r.dataHoraRecebimento BETWEEN :dataInicial and :dataFinal ");
		sql.append("GROUP BY r.pedido_id ORDER BY dataEntrega");
		
		try {
			session = getSessionAberta();
			SQLQuery sqlQuery = session.createSQLQuery(sql.toString());
			sqlQuery.addScalar("numeroPedido", Hibernate.STRING);
			sqlQuery.addScalar("dataEntrega", Hibernate.DATE);
			sqlQuery.addScalar("nome", Hibernate.STRING);
			sqlQuery.addScalar("totalPedido", Hibernate.BIG_DECIMAL);
			sqlQuery.addScalar("valorPago", Hibernate.BIG_DECIMAL);
			sqlQuery.addScalar("valorPendente", Hibernate.BIG_DECIMAL);
			
			sqlQuery.setTimestamp("dataInicial", TreatDate.clearDateTime(dataInicial));
			sqlQuery.setTimestamp("dataFinal", TreatDate.clearLimitDateTime(dataFinal));
			
			sqlQuery.setResultTransformer(new AliasToBeanResultTransformer(RelatorioRecebimentoDTO.class));
			return (ArrayList<RelatorioRecebimentoDTO>) sqlQuery.list();
		} catch (Exception e) {
			throw new ApplicationException(e.getMessage());
		} finally {
			if (session != null)
				session.close();
		}
	}
	
	@SuppressWarnings("unchecked")
	public ArrayList<RelatorioRecebimentoDTO> consultarRecebimentosPendentes(Long codigo) throws ApplicationException {

		Session session = null;
		StringBuilder sql = new StringBuilder();

		sql.append("select pedido_id as numeroPedido, p.dataEntrega, c.nome , p.total as totalPedido, sum(valor) as valorPendente from recebimento r ");
		sql.append("JOIN pedido p on p.codigo = r.pedido_id ");
		sql.append("JOIN cliente c on c.codigo = p.cliente_codigo ");
		sql.append(" where r.pendente = true ");
		if(codigo != null && codigo != 0){
			sql.append(" AND  c.codigo = :codigo ");
		}
		sql.append("GROUP BY r.pedido_id ORDER BY nome");
		
		try {
			session = getSessionAberta();
			SQLQuery sqlQuery = session.createSQLQuery(sql.toString());
			sqlQuery.addScalar("numeroPedido", Hibernate.STRING);
			sqlQuery.addScalar("dataEntrega", Hibernate.DATE);
			sqlQuery.addScalar("nome", Hibernate.STRING);
			sqlQuery.addScalar("totalPedido", Hibernate.BIG_DECIMAL);
			sqlQuery.addScalar("valorPendente", Hibernate.BIG_DECIMAL);
			if(codigo != null && codigo != 0){
				sqlQuery.setLong("codigo", codigo);
			}
			sqlQuery.setResultTransformer(new AliasToBeanResultTransformer(RelatorioRecebimentoDTO.class));
			return (ArrayList<RelatorioRecebimentoDTO>) sqlQuery.list();
		} catch (Exception e) {
			throw new ApplicationException(e.getMessage());
		} finally {
			if (session != null)
				session.close();
		}
	}


	@SuppressWarnings("unchecked")
	public ArrayList<RecebimentoDTO> consultarRecebimentoPorNomeCliente(String nomeCliente) throws ApplicationException {
		Session session = null;

		StringBuilder sql = new StringBuilder();
		sql.append("SELECT rec.codigo AS codigo, ");
		sql.append("rec.valor AS valor, ");
		sql.append("rec.forma AS forma, ");
		sql.append("rec.dataHoraRecebimento AS dataHoraRecebimento, ");
		sql.append("rec.usuario AS codigoUsuario, ");
		sql.append("rec.pendente AS pendente, ");
		sql.append("p.codigo AS codigoPedido, ");
		sql.append("c.nome AS nomeCliente ");
		sql.append("FROM recebimento rec ");
		sql.append("INNER JOIN pedido p ON (p.codigo = rec.pedido_id) ");
		sql.append("INNER JOIN cliente c ON (c.codigo = p.cliente_codigo)");
		if(nomeCliente != null && !nomeCliente.equals("")){
			sql.append(" WHERE c.nome LIKE '%"+nomeCliente+"%'");
		}

		try {
			session = getSessionAberta();
			SQLQuery sqlQuery = session.createSQLQuery(sql.toString());
			sqlQuery.addScalar("codigo", Hibernate.LONG);
			sqlQuery.addScalar("valor", Hibernate.BIG_DECIMAL);
			sqlQuery.addScalar("forma", Hibernate.INTEGER);
			sqlQuery.addScalar("dataHoraRecebimento", Hibernate.DATE);
			sqlQuery.addScalar("codigoPedido", Hibernate.LONG);
			sqlQuery.addScalar("nomeCliente", Hibernate.STRING);
			sqlQuery.addScalar("codigoUsuario", Hibernate.LONG);
			sqlQuery.addScalar("pendente", Hibernate.BOOLEAN);

			sqlQuery.setResultTransformer(new AliasToBeanResultTransformer(RecebimentoDTO.class));
			return (ArrayList<RecebimentoDTO>) sqlQuery.list();
		} catch (Exception e) {
			throw new ApplicationException(e.getMessage());
		} finally {
			if (session != null)
				session.close();
		}
	}

	@SuppressWarnings("unchecked")
	public ArrayList<RecebimentoDTO> consultarRecebimentoPorPedido(Long codigoPedido) throws ApplicationException {
		Session session = null;

		StringBuilder sql = new StringBuilder();
		sql.append("SELECT rec.codigo AS codigo, ");
		sql.append("rec.valor AS valor, ");
		sql.append("rec.forma AS forma, ");
		sql.append("rec.dataHoraRecebimento AS dataHoraRecebimento, ");
		sql.append("rec.usuario AS codigoUsuario, ");
		sql.append("rec.pendente AS pendente, ");
		sql.append("p.codigo AS codigoPedido, ");
		sql.append("c.nome AS nomeCliente ");
		sql.append("FROM recebimento rec ");
		sql.append("INNER JOIN pedido p ON (p.codigo = rec.pedido_id) ");
		sql.append("INNER JOIN cliente c ON (c.codigo = p.cliente_codigo)");
		if(codigoPedido != null && !codigoPedido.equals("")){
			sql.append(" WHERE p.codigo = :codigoPedido");
		}

		try {
			session = getSessionAberta();
			SQLQuery sqlQuery = session.createSQLQuery(sql.toString());
			sqlQuery.addScalar("codigo", Hibernate.LONG);
			sqlQuery.addScalar("valor", Hibernate.BIG_DECIMAL);
			sqlQuery.addScalar("forma", Hibernate.INTEGER);
			sqlQuery.addScalar("dataHoraRecebimento", Hibernate.TIMESTAMP);
			sqlQuery.addScalar("codigoPedido", Hibernate.LONG);
			sqlQuery.addScalar("nomeCliente", Hibernate.STRING);
			sqlQuery.addScalar("codigoUsuario", Hibernate.LONG);
			sqlQuery.addScalar("pendente", Hibernate.BOOLEAN);

			sqlQuery.setLong("codigoPedido", codigoPedido);

			sqlQuery.setResultTransformer(new AliasToBeanResultTransformer(RecebimentoDTO.class));
			return (ArrayList<RecebimentoDTO>) sqlQuery.list();
		} catch (Exception e) {
			throw new ApplicationException(e.getMessage());
		} finally {
			if (session != null)
				session.close();
		}
	}
}
